﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.SqlClient;
namespace QuanLyBanHang.DTO
{
  
    public class KetNoiSQL
    {

        SqlConnection Conn;
        private SqlDataAdapter aDapter;
        private DataTable dt;
        //tao ket noi 
        public KetNoiSQL()
        {
            KetNoi();
        }
        public void KetNoi()
        {
            string strConn = @"Data Source=PHI\SQLEXPRESS;Initial Catalog=qlbh;Integrated Security=True";
            try
            {
                Conn = new SqlConnection(strConn);
                Conn.Open();
               
            }
            finally
            {
                if (Conn !=null)
                {
                    Conn.Close();
                }
            }
        }
        public DataTable LayDuLieu(SqlCommand cmd)
        {
            cmd.Connection = Conn;
            aDapter = new SqlDataAdapter(cmd);
            dt = new DataTable();
            aDapter.Fill(dt);
            return dt;
        }

        public int DongThucThi(SqlCommand cmd)
        {

            if (Conn.State == ConnectionState.Closed)
            {
                Conn.Open();
            }
          cmd.Connection = Conn;
          return cmd.ExecuteNonQuery();
          Conn.Close();
            
        }
        //thuc thi cau lenh sql insert update delete
        public static void LayDuLieu(string select)
        {
            try
            {
                SqlConnection Conn = new SqlConnection();
                if (Conn.State == ConnectionState.Closed)
                {
                    Conn.Open();
                }
                SqlCommand cmd = new SqlCommand(select, Conn);
                cmd.ExecuteNonQuery();
                Conn.Close();

            }
            catch (Exception ex)
            {
                
                throw;
            }
       }
        //thuc thi cau lenh select mot bang 
        public static DataTable LayDuLieuSelect(string select)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlConnection Conn = new SqlConnection();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Conn;
                SqlDataAdapter sqlDa = new SqlDataAdapter(select, Conn);
                sqlDa.Fill(dt);
                return dt;
                Conn.Close();


            }
            catch 
            {

                return null;
            }
        }
        //thuc thi cau lenh dieu kien cua mot truong nao do 
        public static string LayDuLieuMotCot(string strSQl)//select
        {
            string tem = null;
        
            try
            {
                SqlConnection Conn = new SqlConnection();
                if (Conn.State == ConnectionState.Closed)
                {
                    Conn.Open();
                }
                SqlCommand cmd = new SqlCommand(strSQl, Conn);
                SqlDataReader sqlDr = cmd.ExecuteReader();
                while (sqlDr.Read())
                {
                    tem = sqlDr[0].ToString();
                }
             
                Conn.Close();

            }
            catch 
            {
                return null;
                
            }
            return tem;
          
        }
    }
}
